package models

import (
	"github.com/beego/beego/v2/client/orm"
)

type CustomerVoModel struct {
	CustomerID             int    `json:"customerID" orm:"column(customer_ID)"`
	CustomerName           string `json:"customerName" orm:"column(customer_name)"`
	CustomerCompany        string `json:"customerCompany" orm:"column(company_name)"`
	ServiceTimes           int    `json:"serviceTimes" orm:"column(service_times)"`
	CustomerLastFinishTime string `json:"customerLastFinishTime" orm:"column(last_service_finish_time)"`
	CustomerStatus         int    `json:"customerStatus" orm:"column(customer_status)"`
	ServiceStatus          int    `json:"serviceStatus" orm:"column(service_status)"`
}

// GetCustomerListInformationByContactID 查询联系人下客户信息列表
func GetCustomerListInformationByContactID(contactID int, from, to int64) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	var customers []CustomerVoModel
	var total int64
	num, err := o.Raw("SELECT cus.customer_ID, cus.customer_name,cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? GROUP BY cus.customer_ID, cus.customer_name, cus.company_name order by service_status desc , customer_status desc limit ? , ?;", contactID, from, to).QueryRows(&customers)
	err = o.Raw("SELECT COUNT(*) FROM (SELECT cus.customer_ID, cus.customer_name, cus.company_name, MAX(cus.service_times) AS service_times, MAX(cus.last_service_finish_time) AS last_service_finish_time, MAX(cus.customer_status) AS customer_status, MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ?  GROUP BY cus.customer_ID, cus.customer_name, cus.company_name) AS subquery;", contactID).QueryRow(&total)
	if err != nil {
		return nil, num
	} else {
		return customers, total
	}
}

// GetLostCustomerListInformationByContactID 查询联系人下流失客户信息列表
func GetLostCustomerListInformationByContactID(contactID int, from, to int64) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	var customers []CustomerVoModel
	var total int64
	num, err := o.Raw("Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and ser.service_status = 7 and last_service_finish_time < DATE_SUB(now(),interval 6 month) GROUP BY cus.customer_ID, cus.customer_name, cus.company_name limit ? ,?", contactID, from, to).QueryRows(&customers)
	err = o.Raw("SELECT COUNT(*) FROM (Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and ser.service_status = 7 and last_service_finish_time < DATE_SUB(now(),interval 6 month) GROUP BY cus.customer_ID, cus.customer_name, cus.company_name) AS subquery;", contactID).QueryRow(&total)

	if err == nil {
		return customers, total
	} else {
		return []CustomerVoModel{}, num
	}
}

// GetPlanCustomerListInformationByContactID 查询联系人下可开展服务客户信息列表
func GetPlanCustomerListInformationByContactID(contactID int, from, to int64) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	var customers []CustomerVoModel
	var total int64
	num, err := o.Raw("Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and (cus.customer_status = 0 or service_status is null) GROUP BY cus.customer_ID, cus.customer_name, cus.company_name limit ? ,?", contactID, from, to).QueryRows(&customers)
	err = o.Raw("SELECT COUNT(*) FROM (Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and (cus.customer_status = 0 or service_status is null)GROUP BY cus.customer_ID, cus.customer_name, cus.company_name ) AS subquery;", contactID).QueryRow(&total)

	if err == nil {
		return customers, total
	} else {
		return []CustomerVoModel{}, num
	}
}

// GetCustomerListInformationByContactIDCustomer 搜索联系人下客户信息列表
func GetCustomerListInformationByContactIDCustomer(customer string, contactID int) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	customer = "%" + customer + "%"
	var customers []CustomerVoModel
	num, err := o.Raw("SELECT cus.customer_ID, cus.customer_name,cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and customer_name like ? GROUP BY cus.customer_ID, cus.customer_name, cus.company_name order by service_status desc , customer_status desc;", contactID, customer).QueryRows(&customers)
	if err != nil {
		return nil, 0
	} else {
		return customers, num
	}
}

// GetLostCustomerListInformationByContactIDCustomer 搜索联系人下流失客户信息列表
func GetLostCustomerListInformationByContactIDCustomer(customer string, contactID int) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	customer = "%" + customer + "%"
	var customers []CustomerVoModel
	num, err := o.Raw("Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and customer_name like ?  and ser.service_status = 7 and last_service_finish_time < DATE_SUB(now(),interval 6 month) GROUP BY cus.customer_ID, cus.customer_name, cus.company_name", contactID, customer).QueryRows(&customers)
	if err == nil {
		return customers, num
	} else {
		return nil, 0
	}
}

// GetPlanCustomerListInformationByContactIDCustomer 搜索联系人下可开展服务客户信息列表
func GetPlanCustomerListInformationByContactIDCustomer(customer string, contactID int) ([]CustomerVoModel, int64) {
	o := orm.NewOrm()
	customer = "%" + customer + "%"
	var customers []CustomerVoModel
	num, err := o.Raw("Select cus.customer_ID,cus.customer_name, cus.company_name,MAX(cus.service_times) AS service_times,MAX(cus.last_service_finish_time) AS last_service_finish_time,MAX(cus.customer_status) AS customer_status,MAX(ser.service_status) AS service_status FROM contact_customer_mid ccm LEFT JOIN contacts con ON ccm.contact_ID = con.contact_ID LEFT JOIN customers cus ON ccm.customer_ID = cus.customer_ID LEFT JOIN services ser ON ser.customer_ID = cus.customer_ID where con.contact_ID = ? and customer_name like ?  and (cus.customer_status = 0 or service_status is null) GROUP BY cus.customer_ID, cus.customer_name, cus.company_name", contactID, customer).QueryRows(&customers)
	if err == nil {
		return customers, num
	} else {
		return nil, 0
	}
}
